import { Service } from 'egg';
import { Topic } from '../model/Topic';
import { Collection } from '../model/Collection';

class AssuageGriefService extends Service {
    /**
     * 查询解忧列表
     * @param param
     */
    public async searchAssuageGriefList(param: any) {
        const offset = (Number(param.page) - 1) * Number(param.size);
        const limit = Number(param.size);
        const submitData: any = {};
        let queryStr: string = `
            SELECT
                a.assuage_grief_id,
                u.user_id,
                u.user_nickname,
                a.assuage_grief_content,
                a.assuage_grief_picture,
                a.assuage_grief_time,
                a.created_at,
                a.assuage_grief_status,
                a.assuage_grief_anonymous 
            FROM
                (
                (
            SELECT
                a.assuage_grief_id,
                a.topic_id,
                a.assuage_grief_content,
                a.assuage_grief_picture,
                a.assuage_grief_praise,
                a.assuage_grief_comment,
                a.assuage_grief_time,
                a.created_at,
                a.assuage_grief_anonymous,
                a.assuage_grief_status,
                a.user_id,
                a.is_delete 
            FROM
                t_assuage_grief a
                INNER JOIN t_topic t ON a.topic_id = t.topic_id 
                AND t.topic_status = '1' 
                ) UNION
                (
            SELECT
                assuage_grief_id,
                topic_id,
                assuage_grief_content,
                assuage_grief_picture,
                assuage_grief_praise,
                assuage_grief_comment,
                assuage_grief_time,
                created_at,
                assuage_grief_anonymous,
                assuage_grief_status,
                user_id,
                is_delete 
            FROM
                t_assuage_grief 
            WHERE
                (topic_id IS NULL 
                OR topic_id = '') 
                ) 
                ) a
                INNER JOIN t_user u ON a.user_id = u.user_id 
                AND u.user_status <> '1'
                INNER JOIN t_school s ON u.user_school_id = s.school_id 
                AND s.school_status = '1'
                INNER JOIN t_college c ON u.user_college_id = c.college_id 
                AND c.college_status = '1'
            WHERE 
                a.is_delete='1'`;
        if (param.assuage_grief_content) {
            queryStr += ` AND a.assuage_grief_content LIKE :assuage_grief_content`;
            submitData.assuage_grief_content = '%' + param.assuage_grief_content + '%';
        }
        if (param.assuage_grief_time) {
            queryStr += ` AND a.assuage_grief_time LIKE :assuage_grief_time`;
            submitData.assuage_grief_time = '%' + param.assuage_grief_time + '%';
        }
        if (param.topic_id) {
            queryStr += ` AND a.topic_id=:topic_id`;
            submitData.topic_id = param.topic_id;
        }
        const queryCount = queryStr + ` ORDER BY a.assuage_grief_time DESC`;
        queryStr += ` ORDER BY a.assuage_grief_time DESC LIMIT ${offset}, ${limit}`;
        const assuageGrief = await this.ctx.model.query(queryStr, {
            type: this.app.Sequelize.QueryTypes.SELECT,
            replacements: submitData
        });
        const count = await this.ctx.model.query(queryCount, {
            type: this.app.Sequelize.QueryTypes.SELECT,
            replacements: submitData
        });
        if (assuageGrief.length <= 0) {
            throw new Error(JSON.stringify({ code: '2', msg: '查询无记录' }));
        }
        const result = { count: count.length, assuageGriefList: assuageGrief };
        return result;
    }

    /**
     * 根据标题查找话题
     * @param param 
     */
    public async searchTopicByTitle(param: any) {
        const Op = this.ctx.model.Op;
        const submitData: any = {};
        if (!param.topic_title) {
            submitData.topic_status = '1';
        } else {
            submitData.topic_title = { [Op.like]: '%' + param.topic_title + '%' };
            submitData.topic_status = '1';
        }
        const resultTopic = await this.ctx.model.Topic.findAll<Topic>({
            attributes: ['topic_id', 'topic_title'],
            where: submitData
        });
        if (resultTopic.length <= 0) {
            throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
        }
        return resultTopic;
    }

    /**
     * 屏蔽、解除屏蔽
     * @param param 
     */
    public async showAndShieldAssuageGriefer(param: any) {
        const resultAssuageGrief = await this.ctx.model.AssuageGrief.update(
            { assuage_grief_status: param.type },
            { where: { assuage_grief_id: param.assuage_grief_id } }
        );
        if (resultAssuageGrief[0] <= 0) {
            throw new Error(JSON.stringify({ code: 2, msg: '操作失败' }));
        }
    }

    /**
     * 微信端查询全部解忧
     * @param param 
     */
    public async searchAssuageGriefAll(param: any) {
        const offset = (Number(param.page) - 1) * Number(param.size);
        const limit = Number(param.size);
        const submitData: any = {};
        let queryStr: string = `
        SELECT
        a.assuage_grief_id,
        u.user_id,
        u.user_avatarUrl,
        u.user_nickname,
        u.user_sex,
        u.user_sound_url,
        u.user_sound_status,
        u.user_school_year,
        s.school_name,
        s.school_badge,
        c.college_name,
        c.age_limit,
        a.assuage_grief_content,
        a.assuage_grief_picture,
        a.assuage_grief_praise,
        a.assuage_grief_comment,
        a.assuage_grief_time,
        a.assuage_grief_anonymous 
    FROM
    (
        (
    SELECT
        a.assuage_grief_id,
        a.topic_id,
        a.assuage_grief_content,
        a.assuage_grief_picture,
        a.assuage_grief_praise,
        a.assuage_grief_comment,
        a.assuage_grief_time,
        a.assuage_grief_anonymous,
        a.assuage_grief_status,
        a.user_id,
        a.is_delete 
    FROM
        t_assuage_grief a
        INNER JOIN t_topic t ON a.topic_id = t.topic_id 
        AND t.topic_status = '1' 
        ) UNION
        (
    SELECT
        assuage_grief_id,
        topic_id,
        assuage_grief_content,
        assuage_grief_picture,
        assuage_grief_praise,
        assuage_grief_comment,
        assuage_grief_time,
        assuage_grief_anonymous,
        assuage_grief_status,
        user_id,
        is_delete 
    FROM
        t_assuage_grief 
    WHERE
        (topic_id IS NULL 
        OR topic_id = '') 
        ) 
        ) a
        INNER JOIN t_user u ON 
        a.assuage_grief_status = '1'
        AND a.user_id NOT IN (SELECT * FROM(
        ( SELECT friends_target_user_id FROM t_friends WHERE friends_user_id = :user_id AND is_delete = '1' AND friends_status BETWEEN '2' AND '3' ) UNION
        ( SELECT friends_user_id FROM t_friends WHERE friends_target_user_id = :user_id AND is_delete = '1' AND friends_status = '3' ) 
        ) as temp) 
        AND a.user_id = u.user_id 
        AND u.user_status <> '1' 
        AND u.user_school_id =:school_id
        INNER JOIN t_school s ON u.user_school_id = s.school_id 
        AND s.school_status = '1'
        INNER JOIN t_college c ON u.user_college_id = c.college_id 
        AND c.college_status = '1'
        LEFT JOIN t_topic t ON a.topic_id = t.topic_id 
        AND t.topic_status = '1'
        WHERE a.is_delete = '1'`;
        submitData.user_id = param.user_id;
        submitData.school_id = param.school_id;
        if (param.assuage_grief_content) {
            queryStr += ` AND a.assuage_grief_content LIKE :assuage_grief_content `;
            submitData.assuage_grief_content = '%' + param.assuage_grief_content + '%'
        }
        if (param.topic_id) {
            queryStr += ` AND a.topic_id=:topic_id `;
            submitData.topic_id = param.topic_id;
        }
        const queryCount = queryStr + ` ORDER BY a.assuage_grief_time DESC`;
        queryStr += ` ORDER BY a.assuage_grief_time DESC LIMIT ${offset}, ${limit}`;
        submitData.school_id = param.school_id;
        const assuageGrief: any = await this.ctx.model.query(queryStr, {
            type: this.app.Sequelize.QueryTypes.SELECT,
            replacements: submitData
        });
        const count = await this.ctx.model.query(queryCount, {
            type: this.app.Sequelize.QueryTypes.SELECT,
            replacements: submitData
        });
        if (assuageGrief.length <= 0) {
            throw new Error(JSON.stringify({ code: '2', msg: '查询无记录' }));
        }
        let returnData: any = [];
        for (let i = 0; i < assuageGrief.length; i++) {
            const item: any = {};
            item.assuage_grief_id = assuageGrief[i].assuage_grief_id;
            item.user_id = assuageGrief[i].user_id;
            item.user_avatarUrl = assuageGrief[i].user_avatarUrl;
            item.user_nickname = assuageGrief[i].user_nickname;
            item.user_sex = assuageGrief[i].user_sex;
            item.user_sound_url = assuageGrief[i].user_sound_url;
            item.user_sound_status = assuageGrief[i].user_sound_status;
            item.user_school_year = assuageGrief[i].user_school_year;
            item.school_name = assuageGrief[i].school_name;
            item.school_badge = assuageGrief[i].school_badge;
            item.college_name = assuageGrief[i].college_name;
            item.age_limit = assuageGrief[i].age_limit;
            item.assuage_grief_content = assuageGrief[i].assuage_grief_content;
            item.assuage_grief_picture = assuageGrief[i].assuage_grief_picture;
            item.assuage_grief_praise = assuageGrief[i].assuage_grief_praise;
            item.assuage_grief_comment = assuageGrief[i].assuage_grief_comment;
            item.assuage_grief_anonymous = assuageGrief[i].assuage_grief_anonymous;
            item.assuage_grief_time = assuageGrief[i].assuage_grief_time;
            let resultPraise = await this.ctx.service.praiseService.checkIsPraise(param.user_id, item.assuage_grief_id, '2');
            if (!resultPraise) {
                item.is_praise = '2';
            } else {
                item.is_praise = '1';
            }
            let resultCollection = await this.ctx.model.Collection.findOne({ attributes: ['collection_id'], where: { user_id: param.user_id, assuage_grief_id: item.assuage_grief_id, is_delete: '1' } });
            if (!resultCollection) {
                item.is_collection = '2';
            } else {
                item.is_collection = '1';
            }
            returnData.push(item);
        }
        const result = { count: count.length, assuageGriefList: returnData };
        return result;
    }

    /**
     * 处理收藏解忧（收藏、取消收藏）
     * @param param 
     */
    public async handleCollection(param: any) {
        const type = Number(param.type);
        if (type === 1) {
            const result: any = await this.ctx.model.Collection.findOne<Collection>({
                attributes: ['collection_id'],
                where: { user_id: param.user_id, assuage_grief_id: param.assuage_grief_id }
            });
            if (!result) {
                const submitData = new Collection();
                submitData.collection_id = this.ctx.helper.genUUID();
                submitData.user_id = param.user_id;
                submitData.assuage_grief_id = param.assuage_grief_id;
                submitData.is_delete = '1';
                const resultCollection = await this.ctx.model.Collection.create(submitData);
                if (!resultCollection) {
                    throw new Error(JSON.stringify({ code: 2, msg: '收藏失败' }));
                }
            } else {
                const collection_id = result.collection_id;
                const updateCollection = await this.ctx.model.Collection.update({ is_delete: '1' }, { where: { collection_id } });
                if (updateCollection[0] <= 0) {
                    throw new Error(JSON.stringify({ code: 2, msg: '收藏失败' }));
                }
            }
        } else if (type === 2) {
            const resultCollection = await this.ctx.model.Collection.update({ is_delete: '2' }, { where: { user_id: param.user_id, assuage_grief_id: param.assuage_grief_id } });
            if (resultCollection[0] <= 0) {
                throw new Error(JSON.stringify({ code: 2, msg: '取消收藏失败' }));
            }
        }
    }

    /**
     * 微信端查询我关注的朋友的解忧
     * @param param 
     */
    public async searchFriendsAssuageGrief(param: any) {
        const offset = (Number(param.page) - 1) * Number(param.size);
        const limit = Number(param.size);
        const actuleParam: any = {};
        let queryStr: string = `
            SELECT
                * 
            FROM (
                (
            SELECT
                a.assuage_grief_id,
                a.topic_id,
                a.assuage_grief_content,
                a.assuage_grief_picture,
                a.assuage_grief_praise,
                a.assuage_grief_comment,
                a.assuage_grief_time,
                a.assuage_grief_anonymous,
                a.assuage_grief_status,
                a.user_id,
                a.is_delete 
            FROM
                t_assuage_grief a
                INNER JOIN t_topic t ON a.topic_id = t.topic_id 
                AND t.topic_status = '1' 
                ) UNION
                (
            SELECT
                assuage_grief_id,
                topic_id,
                assuage_grief_content,
                assuage_grief_picture,
                assuage_grief_praise,
                assuage_grief_comment,
                assuage_grief_time,
                assuage_grief_anonymous,
                assuage_grief_status,
                user_id,
                is_delete 
            FROM
                t_assuage_grief 
            WHERE
                (topic_id IS NULL 
                OR topic_id = '') 
                ) 
                ) a INNER JOIN
                (
            SELECT
                friends_target_user_id 
            FROM
                (SELECT * FROM v_friends_eachother) temp 
            WHERE
                friends_user_id = :user_id 
                ) AS f ON a.user_id=f.friends_target_user_id
                INNER JOIN t_user u ON f.friends_target_user_id = u.user_id 
                AND u.user_status <> '1'
                INNER JOIN t_school s ON u.user_school_id = s.school_id 
                AND s.school_status = '1'
                INNER JOIN t_college c ON u.user_college_id = c.college_id 
                AND c.college_status = '1'
                WHERE a.assuage_grief_anonymous = '2' AND a.assuage_grief_status='1' AND a.is_delete='1'`;
        actuleParam.user_id = param.user_id;
        if (param.assuage_grief_content) {
            queryStr += `AND a.assuage_grief_content LIKE :assuage_grief_content`;
            actuleParam.assuage_grief_content = '%' + param.assuage_grief_content + '%';
        }
        if (param.topic_id) {
            queryStr += ` AND a.topic_id=:topic_id`;
            actuleParam.topic_id = param.topic_id;
        }
        const queryCount = queryStr + ` ORDER BY a.assuage_grief_time DESC`;
        queryStr += ` ORDER BY a.assuage_grief_time DESC LIMIT ${offset},${limit}`
        const assuageGrief: any = await this.ctx.model.query(queryStr, {
            type: this.app.Sequelize.QueryTypes.SELECT,
            replacements: actuleParam
        });
        const count = await this.ctx.model.query(queryCount, {
            type: this.app.Sequelize.QueryTypes.SELECT,
            replacements: actuleParam
        });
        if (assuageGrief.length <= 0) {
            throw new Error(JSON.stringify({ code: '2', msg: '查询无记录' }));
        }
        let returnData: any = [];
        for (let i = 0; i < assuageGrief.length; i++) {
            const item: any = {};
            item.assuage_grief_id = assuageGrief[i].assuage_grief_id;
            item.user_id = assuageGrief[i].user_id;
            item.user_avatarUrl = assuageGrief[i].user_avatarUrl;
            item.user_nickname = assuageGrief[i].user_nickname;
            item.user_sex = assuageGrief[i].user_sex;
            item.user_sound_url = assuageGrief[i].user_sound_url;
            item.user_sound_status = assuageGrief[i].user_sound_status;
            item.user_school_year = assuageGrief[i].user_school_year;
            item.school_name = assuageGrief[i].school_name;
            item.school_badge = assuageGrief[i].school_badge;
            item.college_name = assuageGrief[i].college_name;
            item.age_limit = assuageGrief[i].age_limit;
            item.assuage_grief_content = assuageGrief[i].assuage_grief_content;
            item.assuage_grief_picture = assuageGrief[i].assuage_grief_picture;
            item.assuage_grief_praise = assuageGrief[i].assuage_grief_praise;
            item.assuage_grief_comment = assuageGrief[i].assuage_grief_comment;
            item.assuage_grief_time = assuageGrief[i].assuage_grief_time;
            item.assuage_grief_anonymous = assuageGrief[i].assuage_grief_anonymous;
            let resultPraise = await this.ctx.service.praiseService.checkIsPraise(param.user_id, item.assuage_grief_id, '2');
            if (!resultPraise) {
                item.is_praise = '2';
            } else {
                item.is_praise = '1';
            }
            let resultCollection = await this.ctx.model.Collection.findOne({ attributes: ['collection_id'], where: { user_id: param.user_id, assuage_grief_id: item.assuage_grief_id, is_delete: '1' } });
            if (!resultCollection) {
                item.is_collection = '2';
            } else {
                item.is_collection = '1';
            }
            returnData.push(item);
        }
        const result = { count: count.length, assuageGriefList: returnData };
        return result;
    }

    /**
     * 查找我的解忧
     * @param param 
     */
    public async searchMyAssuageGrief(param: any) {
        const offset = (Number(param.page) - 1) * Number(param.size);
        const limit = Number(param.size);
        let actuleParam: any = {};
        let queryStr: string = `SELECT a.assuage_grief_id,u.user_id,u.user_avatarUrl,u.user_nickname,u.user_sex,u.user_sound_url,u.user_sound_status,u.user_school_year,s.school_name,s.school_badge,c.college_name,`
            + `a.assuage_grief_content,a.assuage_grief_picture,a.assuage_grief_praise,a.assuage_grief_comment,a.assuage_grief_time,a.assuage_grief_anonymous`
            + ` from (
                (
            SELECT
                a.assuage_grief_id,
                a.topic_id,
                a.assuage_grief_content,
                a.assuage_grief_picture,
                a.assuage_grief_praise,
                a.assuage_grief_comment,
                a.assuage_grief_time,
                a.assuage_grief_anonymous,
                a.assuage_grief_status,
                a.user_id,
                a.is_delete 
            FROM
                t_assuage_grief a
                INNER JOIN t_topic t ON a.topic_id = t.topic_id 
                AND t.topic_status = '1' 
                ) UNION
                (
            SELECT
                assuage_grief_id,
                topic_id,
                assuage_grief_content,
                assuage_grief_picture,
                assuage_grief_praise,
                assuage_grief_comment,
                assuage_grief_time,
                assuage_grief_anonymous,
                assuage_grief_status,
                user_id,
                is_delete 
            FROM
                t_assuage_grief 
            WHERE
                (topic_id IS NULL 
                OR topic_id = '') 
                ) 
                ) a inner join t_user u on a.is_delete='1' and a.assuage_grief_status='1' `;
        if (param.user_id) {
            queryStr += `and a.user_id=:user_id`;
            actuleParam.user_id = param.user_id;
        }
        queryStr += ` and a.user_id= u.user_id inner join t_school s on u.user_school_id=s.school_id and s.school_status='1'`
            + ` inner join t_college c on u.user_college_id=c.college_id and c.college_status='1' order by a.assuage_grief_time DESC`
        const queryCount = queryStr + ` limit ${offset}, ${limit}`;
        const assuageGrief: any = await this.ctx.model.query(queryCount, {
            type: this.app.Sequelize.QueryTypes.SELECT,
            replacements: actuleParam
        });
        const count = await this.ctx.model.query(queryStr, {
            type: this.app.Sequelize.QueryTypes.SELECT,
            replacements: actuleParam
        });
        if (assuageGrief.length <= 0) {
            throw new Error(JSON.stringify({ code: '2', msg: '查询无记录' }));
        }
        let returnData: any = [];
        for (let i = 0; i < assuageGrief.length; i++) {
            const item: any = {};
            item.assuage_grief_id = assuageGrief[i].assuage_grief_id;
            item.user_id = assuageGrief[i].user_id;
            item.user_avatarUrl = assuageGrief[i].user_avatarUrl;
            item.user_nickname = assuageGrief[i].user_nickname;
            item.user_sex = assuageGrief[i].user_sex;
            item.user_sound_url = assuageGrief[i].user_sound_url;
            item.user_sound_status = assuageGrief[i].user_sound_status;
            item.user_school_year = assuageGrief[i].user_school_year;
            item.school_name = assuageGrief[i].school_name;
            item.school_badge = assuageGrief[i].school_badge;
            item.college_name = assuageGrief[i].college_name;
            item.assuage_grief_content = assuageGrief[i].assuage_grief_content;
            item.assuage_grief_picture = assuageGrief[i].assuage_grief_picture;
            item.assuage_grief_praise = assuageGrief[i].assuage_grief_praise;
            item.assuage_grief_comment = assuageGrief[i].assuage_grief_comment;
            item.assuage_grief_time = assuageGrief[i].assuage_grief_time;
            item.assuage_grief_anonymous = assuageGrief[i].assuage_grief_anonymous;
            let resultPraise = await this.ctx.service.praiseService.checkIsPraise(param.user_id, item.assuage_grief_id, '2');
            if (!resultPraise) {
                item.is_praise = '2';
            } else {
                item.is_praise = '1';
            }
            let resultCollection = await this.ctx.model.Collection.findOne({ attributes: ['collection_id'], where: { user_id: param.user_id, assuage_grief_id: item.assuage_grief_id, is_delete: '1' } });
            if (!resultCollection) {
                item.is_collection = '2';
            } else {
                item.is_collection = '1';
            }
            returnData.push(item);
        }
        const result = { count: count.length, assuageGriefList: returnData };
        return result;
    }

    /**
     * 根据用户id查询解忧列表
     * @param param 
     */
    public async searchAssuageGriefListById(param: any) {
        let { target_user_id, user_id } = param;
        if (!user_id) {
            user_id = '';
        }
        const offset = (Number(param.page) - 1) * Number(param.size);
        const limit = Number(param.size);
        let actuleParam: any = {};
        let queryStr: string = `SELECT a.assuage_grief_id,u.user_id,u.user_avatarUrl,u.user_nickname,u.user_sex,u.user_sound_url,u.user_sound_status,u.user_school_year,s.school_name,s.school_badge,c.college_name,`
            + `a.assuage_grief_content,a.assuage_grief_picture,a.assuage_grief_praise,a.assuage_grief_comment,a.assuage_grief_time,a.assuage_grief_anonymous`
            + ` from (
                (
            SELECT
                a.assuage_grief_id,
                a.topic_id,
                a.assuage_grief_content,
                a.assuage_grief_picture,
                a.assuage_grief_praise,
                a.assuage_grief_comment,
                a.assuage_grief_time,
                a.assuage_grief_anonymous,
                a.assuage_grief_status,
                a.user_id,
                a.is_delete 
            FROM
                t_assuage_grief a
                INNER JOIN t_topic t ON a.topic_id = t.topic_id 
                AND t.topic_status = '1' 
                ) UNION
                (
            SELECT
                assuage_grief_id,
                topic_id,
                assuage_grief_content,
                assuage_grief_picture,
                assuage_grief_praise,
                assuage_grief_comment,
                assuage_grief_time,
                assuage_grief_anonymous,
                assuage_grief_status,
                user_id,
                is_delete 
            FROM
                t_assuage_grief 
            WHERE
                (topic_id IS NULL 
                OR topic_id = '') 
                ) 
                ) a inner join t_user u on a.is_delete='1' and a.assuage_grief_status='1' and a.assuage_grief_anonymous='2' `;
        if (target_user_id) {
            queryStr += `and a.user_id=:user_id`;
            actuleParam.user_id = target_user_id;
        }
        queryStr += ` and a.user_id= u.user_id inner join t_school s on u.user_school_id=s.school_id and s.school_status='1'`
            + ` inner join t_college c on u.user_college_id=c.college_id and c.college_status='1' order by a.assuage_grief_time DESC`
        const queryCount = queryStr + ` limit ${offset}, ${limit}`;
        const assuageGrief: any = await this.ctx.model.query(queryCount, {
            type: this.app.Sequelize.QueryTypes.SELECT,
            replacements: actuleParam
        });
        const count = await this.ctx.model.query(queryStr, {
            type: this.app.Sequelize.QueryTypes.SELECT,
            replacements: actuleParam
        });
        if (assuageGrief.length <= 0) {
            throw new Error(JSON.stringify({ code: '2', msg: '查询无记录' }));
        }
        let returnData: any = [];
        for (let i = 0; i < assuageGrief.length; i++) {
            const item: any = {};
            item.assuage_grief_id = assuageGrief[i].assuage_grief_id;
            item.user_id = assuageGrief[i].user_id;
            item.user_avatarUrl = assuageGrief[i].user_avatarUrl;
            item.user_nickname = assuageGrief[i].user_nickname;
            item.user_sex = assuageGrief[i].user_sex;
            item.user_sound_url = assuageGrief[i].user_sound_url;
            item.user_sound_status = assuageGrief[i].user_sound_status;
            item.user_school_year = assuageGrief[i].user_school_year;
            item.school_name = assuageGrief[i].school_name;
            item.school_badge = assuageGrief[i].school_badge;
            item.college_name = assuageGrief[i].college_name;
            item.assuage_grief_content = assuageGrief[i].assuage_grief_content;
            item.assuage_grief_picture = assuageGrief[i].assuage_grief_picture;
            item.assuage_grief_praise = assuageGrief[i].assuage_grief_praise;
            item.assuage_grief_comment = assuageGrief[i].assuage_grief_comment;
            item.assuage_grief_time = assuageGrief[i].assuage_grief_time;
            item.assuage_grief_anonymous = assuageGrief[i].assuage_grief_anonymous;
            let resultPraise = await this.ctx.service.praiseService.checkIsPraise(user_id, item.assuage_grief_id, '2');
            if (!resultPraise) {
                item.is_praise = '2';
            } else {
                item.is_praise = '1';
            }
            let resultCollection = await this.ctx.model.Collection.findOne({ attributes: ['collection_id'], where: { user_id: user_id, assuage_grief_id: item.assuage_grief_id, is_delete: '1' } });
            if (!resultCollection) {
                item.is_collection = '2';
            } else {
                item.is_collection = '1';
            }
            returnData.push(item);
        }
        const result = { count: count.length, assuageGriefList: returnData };
        return result;
    }

    /**
     * 删除解忧
     * @param param 
     */
    public async deleteAssuageGrief(param: any) {
        const result: any = await this.ctx.model.AssuageGrief.update({ is_delete: '2' }, { where: { assuage_grief_id: param.assuage_grief_id, user_id: param.user_id } });
        if (result[0] <= 0) {
            throw new Error(JSON.stringify({ code: 2, msg: '删除失败' }));
        }
    }

    /**
     * 查找我收藏的解忧
     * @param param 
     */
    public async searchCollectionAssuageGrief(param: any) {
        const offset = (Number(param.page) - 1) * Number(param.size);
        const limit = Number(param.size);
        let queryStr: string = `
            SELECT
                a.assuage_grief_id,
                u.user_id,
                u.user_avatarUrl,
                u.user_nickname,
                u.user_sex,
                u.user_sound_url,
                u.user_sound_status,
                u.user_school_year,
                s.school_name,
                s.school_badge,
                c.college_name,
                a.assuage_grief_content,
                a.assuage_grief_picture,
                a.assuage_grief_praise,
                a.assuage_grief_comment,
                a.assuage_grief_time,
                a.assuage_grief_anonymous 
            FROM
                (
                (
            SELECT
                a.assuage_grief_id,
                a.topic_id,
                a.assuage_grief_content,
                a.assuage_grief_picture,
                a.assuage_grief_praise,
                a.assuage_grief_comment,
                a.assuage_grief_time,
                a.assuage_grief_anonymous,
                a.assuage_grief_status,
                a.user_id,
                a.is_delete 
            FROM
                t_assuage_grief a
                INNER JOIN t_topic t ON a.topic_id = t.topic_id 
                AND t.topic_status = '1' 
                ) UNION
                (
            SELECT
                assuage_grief_id,
                topic_id,
                assuage_grief_content,
                assuage_grief_picture,
                assuage_grief_praise,
                assuage_grief_comment,
                assuage_grief_time,
                assuage_grief_anonymous,
                assuage_grief_status,
                user_id,
                is_delete 
            FROM
                t_assuage_grief 
            WHERE
                (topic_id IS NULL 
                OR topic_id = '') 
                ) 
                ) a
                INNER JOIN t_collection cl ON cl.user_id = :user_id 
                AND cl.is_delete = '1' 
                AND a.assuage_grief_status = '1' 
                AND cl.assuage_grief_id = a.assuage_grief_id
                INNER JOIN t_user u ON a.user_id = u.user_id 
                AND u.user_status <> '1' 
                AND a.user_id NOT IN (
            SELECT
                friends_target_user_id 
            FROM
                t_friends 
            WHERE
                friends_user_id = :user_id 
                AND friends_status BETWEEN '2' 
                AND '3' UNION
            SELECT
                friends_user_id 
            FROM
                t_friends 
            WHERE
                friends_target_user_id = :user_id 
                AND friends_status = '3' 
                )
                INNER JOIN t_school s ON u.user_school_id = s.school_id 
                AND s.school_status = '1'
                INNER JOIN t_college c ON u.user_college_id = c.college_id 
                AND c.college_status = '1' 
            WHERE
                a.is_delete = '1'`;
        const queryCount = queryStr + ` ORDER BY a.assuage_grief_time DESC`;
        queryStr += ` ORDER BY a.assuage_grief_time DESC LIMIT ${offset}, ${limit}`;
        const submitData: any = { user_id: param.user_id };
        const assuageGrief: any = await this.ctx.model.query(queryStr, {
            type: this.app.Sequelize.QueryTypes.SELECT,
            replacements: submitData
        });
        const count = await this.ctx.model.query(queryCount, {
            type: this.app.Sequelize.QueryTypes.SELECT,
            replacements: submitData
        });
        if (assuageGrief.length <= 0) {
            throw new Error(JSON.stringify({ code: '2', msg: '查询无记录' }));
        }
        let returnData: any = [];
        for (let i = 0; i < assuageGrief.length; i++) {
            const item: any = {};
            item.assuage_grief_id = assuageGrief[i].assuage_grief_id;
            item.user_id = assuageGrief[i].user_id;
            item.user_avatarUrl = assuageGrief[i].user_avatarUrl;
            item.user_nickname = assuageGrief[i].user_nickname;
            item.user_sex = assuageGrief[i].user_sex;
            item.user_sound_url = assuageGrief[i].user_sound_url;
            item.user_sound_status = assuageGrief[i].user_sound_status;
            item.user_school_year = assuageGrief[i].user_school_year;
            item.school_name = assuageGrief[i].school_name;
            item.school_badge = assuageGrief[i].school_badge;
            item.college_name = assuageGrief[i].college_name;
            item.assuage_grief_content = assuageGrief[i].assuage_grief_content;
            item.assuage_grief_picture = assuageGrief[i].assuage_grief_picture;
            item.assuage_grief_praise = assuageGrief[i].assuage_grief_praise;
            item.assuage_grief_comment = assuageGrief[i].assuage_grief_comment;
            item.assuage_grief_time = assuageGrief[i].assuage_grief_time;
            item.assuage_grief_anonymous = assuageGrief[i].assuage_grief_anonymous;
            let resultPraise = await this.ctx.service.praiseService.checkIsPraise(param.user_id, item.assuage_grief_id, '2');
            if (!resultPraise) {
                item.is_praise = '2';
            } else {
                item.is_praise = '1';
            }
            let resultCollection = await this.ctx.model.Collection.findOne({ attributes: ['collection_id'], where: { user_id: param.user_id, assuage_grief_id: item.assuage_grief_id, is_delete: '1' } });
            if (!resultCollection) {
                item.is_collection = '2';
            } else {
                item.is_collection = '1';
            }
            returnData.push(item);
        }
        const result = { count: count.length, assuageGriefList: returnData };
        return result;
    }

    /**
     * 根据解忧id查找解忧详情
     * @param param 
     */
    public async searchAssuageGriefById(param: any) {
        const queryStr = `SELECT u.user_id,u.user_avatarUrl,u.user_nickname,u.user_sex,u.user_sound_url,u.user_sound_status,u.user_school_year,s.school_name,s.school_badge,c.college_name,a.assuage_grief_id,
        a.assuage_grief_content,a.assuage_grief_praise,a.assuage_grief_comment,a.assuage_grief_anonymous,a.created_at FROM t_assuage_grief a INNER JOIN t_user u ON a.user_id = u.user_id
         INNER JOIN t_school s ON u.user_school_id = s.school_id AND s.school_status = '1' INNER JOIN t_college c ON u.user_college_id = c.college_id AND c.college_status = '1'
         WHERE a.assuage_grief_status='1' and a.assuage_grief_id=:assuage_grief_id`;
        const resultAssuageGrief: any = await this.ctx.model.query(queryStr, {
            type: this.ctx.model.QueryTypes.SELECT,
            replacements: { assuage_grief_id: param.assuage_grief_id }
        })
        if (resultAssuageGrief.length <= 0) {
            throw new Error(JSON.stringify({ code: 2, msg: '没有数据' }));
        }
        const returnData: any = [];
        for (let i = 0; i < resultAssuageGrief.length; i++) {
            const item: any = {};
            item.user_id = resultAssuageGrief[i].user_id;
            item.user_avatarUrl = resultAssuageGrief[i].user_avatarUrl;
            item.user_nickname = resultAssuageGrief[i].user_nickname;
            item.user_sex = resultAssuageGrief[i].user_sex;
            item.user_sound_url = resultAssuageGrief[i].user_sound_url;
            item.user_sound_status = resultAssuageGrief[i].user_sound_status;
            item.user_school_year = resultAssuageGrief[i].user_school_year;
            item.school_name = resultAssuageGrief[i].school_name;
            item.school_badge = resultAssuageGrief[i].school_badge;
            item.college_name = resultAssuageGrief[i].college_name;
            item.assuage_grief_id = resultAssuageGrief[i].assuage_grief_id;
            item.assuage_grief_content = resultAssuageGrief[i].assuage_grief_content;
            item.assuage_grief_praise = resultAssuageGrief[i].assuage_grief_praise;
            item.assuage_grief_comment = resultAssuageGrief[i].assuage_grief_comment;
            item.assuage_grief_anonymous = resultAssuageGrief[i].assuage_grief_anonymous;
            item.created_at = resultAssuageGrief[i].created_at;
            const resultPraise = await this.ctx.service.praiseService.checkIsPraise(param.user_id, item.assuage_grief_id, '2');
            if (resultPraise) {
                item.is_praise = '1';
            } else {
                item.is_praise = '2';
            }
            returnData.push(item);
        }
        return returnData;
    }

    /**
     * 发布解忧
     * @param param 
     */
    public async createAssuageGrief(param: any) {
        const submitData: any = {};
        submitData.assuage_grief_id = this.ctx.helper.genUUID();
        submitData.user_id = param.user_id;
        if (param.topic_id) {
            submitData.topic_id = param.topic_id;
        } else {
            submitData.topic_id = '';
        }
        submitData.assuage_grief_content = param.assuage_grief_content;
        if (param.assuage_grief_picture) {
            submitData.assuage_grief_picture = param.assuage_grief_picture;
        } else {
            submitData.assuage_grief_picture = '';
        }
        submitData.assuage_grief_anonymous = param.assuage_grief_anonymous;
        const date = new Date();
        const dateStr = date.toLocaleString();
        submitData.assuage_grief_time = dateStr;
        submitData.assuage_grief_praise = 0;
        submitData.assuage_grief_comment = 0;
        submitData.assuage_grief_status = '1';
        submitData.is_delete = '1';
        const result = await this.ctx.model.AssuageGrief.create(submitData);
        if (!result) {
            throw new Error(JSON.stringify({ code: 2, msg: '发布失败' }));
        }
    }
}
export default AssuageGriefService;